package com.chunlin;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.mysql.cj.jdbc.MysqlDataSource;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

class Message {
    private String from;
    private String to;
    private String message;

    @Override
    public String toString() {
        return "Message{" +
                "from='" + from + '\'' +
                ", to='" + to + '\'' +
                ", message='" + message + '\'' +
                '}';
    }

    public String getFrom() {
        return from;
    }

    public void setFrom(String from) {
        this.from = from;
    }

    public String getTo() {
        return to;
    }

    public void setTo(String to) {
        this.to = to;
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }
}

@WebServlet("/messageWall")
public class MessageWallServlet extends HttpServlet {
    private ObjectMapper objectMapper = new ObjectMapper();
    private ArrayList<Message> messages = new ArrayList<Message>();

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 1. 读取请求 body, 转换成 java 对象
        Message message = objectMapper.readValue(req.getInputStream(), Message.class);
        //// 2. 得到 message 了之后, 把这个 message 保存到服务器中的内存之中
        //messages.add(message);

        // 2. 得到 message 了之后, 把这个 message 保存到服务器中的数据库之中
        try {
            messageSave(message);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        System.out.println("服务器收到message: " + message);
        //3.返回响应
        resp.setStatus(200);
    }

    private void messageSave(Message message) throws SQLException {
        // 通过 jdbc, 往数据库中存一个数据
        // 1. 创建数据源
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/message_wall?characterEncoding=utf8&useSSL=false");
        dataSource.setUser("root");
        dataSource.setPassword("123456");
        //2.建立连接
        Connection connection = dataSource.getConnection();
        //3.构造sql
        String sql = "insert into message (`from`, `to`, `message`) values (?, ?, ?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, message.getFrom());
        statement.setString(2, message.getTo());
        statement.setString(3, message.getMessage());
        //4.执行sql
        //使用 executeQuery() 当你确定 SQL 语句是一个 SELECT 查询，需要返回数据结果集时。
        //使用 executeUpdate() 当你的 SQL 语句是 INSERT, UPDATE, DELETE, 或者某些 DDL 语句，需要知道受影响的行数时。=
        //使用 execute() 当你执行的 SQL 语句可能返回多种类型的结果，或者当你不确定 SQL 语句的类型时
        //这是最灵活的执行方法，适用于复杂的执行场景，如调用存储过程。
        statement.executeUpdate();
        //5.释放资源,关闭连接
        statement.close();
        connection.close();
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //把数据库中的Message,组织成json格式,返回到响应中
        ArrayList<Message> messageArrayList = null;
        try {
            messageArrayList = messageLoad();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        ////把内存中的Message,组织成json格式,返回到响应中
        //String respJson = objectMapper.writeValueAsString(messages);
        String respJson = objectMapper.writeValueAsString(messageArrayList);
        resp.setContentType("application/json; charset=utf8");
        resp.getWriter().write(respJson);
    }

    private ArrayList<Message> messageLoad() throws SQLException {
        //通过jdbc,从数据库中读取出数据
        //1. 创建数据源
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        mysqlDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/message_wall?characterEncoding=utf8&useSSL=false");
        mysqlDataSource.setUser("root");
        mysqlDataSource.setPassword("123456");
        //2.建立连接
        Connection connection = mysqlDataSource.getConnection();
        //3.构造sql
        String sql = "select * from message";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //4.执行sql
        ResultSet resultSet = preparedStatement.executeQuery();
        //5.遍历结果集合
        ArrayList<Message> messageArrayList = new ArrayList<>();
        while (resultSet.next()) {
            Message message = new Message();
            message.setFrom(resultSet.getString("from"));
            message.setTo(resultSet.getString("to"));
            message.setMessage(resultSet.getString("message"));
            messageArrayList.add(message);
        }
        //6.关闭连接,释放资源,并返回结果
        resultSet.close();
        preparedStatement.close();
        connection.close();
        return messageArrayList;
    }

    @Override
    protected void doDelete(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //通过jdbc,删除数据库中最新的一条数据
        //1.创建数据源
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        mysqlDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/message_wall?characterEncoding=utf8&useSSL=false");
        mysqlDataSource.setUser("root");
        mysqlDataSource.setPassword("123456");


        //2.建立连接
        try {
            try (Connection connection = mysqlDataSource.getConnection()) {
                //3.构造sql
                //高版本的mysql,不允许在同一个查询中对同一个表进行子查询和更新操作,所以下面的写法,会报错
                //String sql = "delete from message where id = (select MAX(id) from message)";
                //PreparedStatement preparedStatement = connection.prepareStatement(sql);
                ////4.执行sql
                //preparedStatement.executeUpdate();
                ////5.释放资源,关闭连接
                //preparedStatement.close();
                //connection.close();

                //4.执行sql
                //获取当前id最大的一条数据
                String getMaxIdSql = "select MAX(id) from message";
                try (PreparedStatement getMaxIdStatement = connection.prepareStatement(getMaxIdSql)) {
                    try (ResultSet resultSet = getMaxIdStatement.executeQuery()) {
                        if (resultSet.next()) {
                            int maxId = resultSet.getInt(1);
                            //获取获取到的最大id来执行删除的语句
                            String deleteMessage = "delete from message where id = ?";
                            try (PreparedStatement deleteMessageStatement = connection.prepareStatement(deleteMessage)) {
                                deleteMessageStatement.setInt(1, maxId);
                                deleteMessageStatement.executeUpdate();
                            }
                        }
                    }
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
